package com.sdyeda.readiot.iotinfo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.sdyeda.readiot.config.Setting;
import com.sdyeda.readiot.data.SetSave;

public class JdbcIotInfo {

	public static List<IotInfo> getall() {

		JdbcTemplate jdbcTemplate = InitJdbc();
		if (jdbcTemplate == null) {
			return null;
		}

		String sql = (String) Setting.Get(Setting.SQL_GETIOTINFO);
		//System.out.println(sql);

		try {
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper<IotInfo>(IotInfo.class));
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
	}
	
	public static List<IotInfo> getall(String driverclassname,String url,String username,String password,String sql) {

		JdbcTemplate jdbcTemplate = InitJdbc(driverclassname,url,username,password);
		if (jdbcTemplate == null) {
			return null;
		}

		try {
			return jdbcTemplate.query(sql, new BeanPropertyRowMapper<IotInfo>(IotInfo.class));
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
	}

	static public int write(IotData data) {

		String type = data.getType();

		@SuppressWarnings("unchecked")
		Map<String,SetSave> iottypes = (Map<String,SetSave>)Setting.Get(Setting.IOTTYPE);
		//Map<String, String> writesqls = (Map<String, String>) Setting.Get(Setting.SQL_WRITEDATA);
		// System.out.println(writesqls);
		//String writesql = writesqls.get("sql" + type);
		String writesql = iottypes.get(type).getWritedata();
		String[] sqls = writesql.split("---"); // 解析多条sql语句
		int result = 0;

		for (int i = 0; i < sqls.length; i++) { // 多条sql语句
			if (FindArgnum(sqls[i]) == 2) {
				if (sqls[i].toUpperCase().contains("CALL")) { // 存储过程
					result += CallProc(sqls[i], data.getId(), data.getOffset());
				} else {
					result += Update(sqls[i], data.getId(), data.getOffset());
				}
			}
			if (FindArgnum(sqls[i]) == 3) {
				if (sqls[i].toUpperCase().contains("CALL")) { // 存储过程
					result += CallProc(sqls[i], data.getId(), data.getValue(), data.getOffset());
				} else {
					result += Update(sqls[i], data.getId(), data.getValue(), data.getOffset());
				}
			}
		}

		return result;
	}

	public static int CallProc(String sql, String id, double... values) {
		try {
			Class.forName((String) Setting.Get(Setting.DATASOURCE_DRIVER));
			Connection connection = DriverManager.getConnection((String) Setting.Get(Setting.DATASOURCE_URL),
					(String) Setting.Get(Setting.DATASOURCE_USERNAME),
					(String) Setting.Get(Setting.DATASOURCE_PASSWORD));

			CallableStatement proc = connection.prepareCall(sql);
			proc.setString(1, id);
			for (int i = 0; i < values.length; i++) {
				// System.out.print(values[i]);
				proc.setDouble(i + 2, values[i]);
			}
			// proc.registerOutParameter(values.length+2, Types.INTEGER);
			proc.execute();
			// int result = proc.getInt(3);
			// System.out.println(proc.getInt(values.length+2));

			proc.close();
			connection.close();
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}
		return 1;
	}

	public static List<IotSavedData> readSavedDate(IotData iotdata) {
		JdbcTemplate jdbcTemplate = InitJdbc();
		if (jdbcTemplate == null) {
			return null;
		}
		@SuppressWarnings("unchecked")
		Map<String,SetSave> iottypes = (Map<String,SetSave>)Setting.Get(Setting.IOTTYPE);
		//Map<String, String> writesqls = (Map<String, String>) Setting.Get(Setting.SQL_WRITEDATA);
		// System.out.println(writesqls);
		//String writesql = writesqls.get("sql" + type);
		
//		Map<String, String> readsqls = (Map<String, String>) Setting.Get(Setting.SQL_READDATA);
//		String readsql = readsqls.get("sql" + iotdata.getType().trim());
		String readsql = iottypes.get(iotdata.getType().trim()).getReaddata();
		if(readsql == null) {
			return null;
		}
		readsql = readsql.replace("?", "'" + iotdata.getId().trim() + "'");

		// Object args[]= new Object[1];
		// args[0] = iotdata.getId();
		// jdbcTemplate.query(readsql, args, new
		// BeanPropertyRowMapper<IotSavedData>(IotSavedData.class));

		try {
			return jdbcTemplate.query(readsql, new BeanPropertyRowMapper<IotSavedData>(IotSavedData.class));
		} catch (Exception e) {
			System.out.println(e);
			return null;
		}
	}

	public static int Update(String sql, String id, double... values) {

		Object[] args = new Object[values.length + 1];
		args[0] = id;
		for (int i = 0; i < values.length; i++) {
			args[i + 1] = values[i];
		}

		JdbcTemplate jdbcTemplate = InitJdbc();
		if (jdbcTemplate == null) {
			return 0;
		}
		try {
			return jdbcTemplate.update(sql, args);
		} catch (Exception e) {
			System.out.println(e);
			return 0;
		}

	}

	static int FindArgnum(String sql) {
		return sql.length() - sql.replace("?", "").length();
	}

	static JdbcTemplate InitJdbc() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
	
		dataSource.setDriverClassName((String) Setting.Get(Setting.DATASOURCE_DRIVER));
		dataSource.setUrl((String) Setting.Get(Setting.DATASOURCE_URL));
		dataSource.setUsername((String) Setting.Get(Setting.DATASOURCE_USERNAME));
		dataSource.setPassword((String) Setting.Get(Setting.DATASOURCE_PASSWORD));

		
		// 常见JdbcTemplate对象，设置数据源
		return new JdbcTemplate(dataSource);
	}
	
	static JdbcTemplate InitJdbc(String driverclassname,String url,String username,String password) {

		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		
		dataSource.setDriverClassName(driverclassname);
		dataSource.setUrl(url);
		dataSource.setUsername(username);
		dataSource.setPassword(password);
		
		// 常见JdbcTemplate对象，设置数据源
		return new JdbcTemplate(dataSource);
	}
	
	static public Boolean TestConnent(String driverclassname,String url,String username,String password) {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		
		dataSource.setDriverClassName(driverclassname);
		dataSource.setUrl(url);
		dataSource.setUsername(username);
		dataSource.setPassword(password);
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			if(conn!=null) {
				conn.close();
				return true;
			}
			else {
				return false;
			}
		} catch (SQLException e) {
			return false;
		}
		
	}

}
